package com.example.mobifin_busticket.controller.databasemanager;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.text.TextUtils;
import android.util.Log;

import com.example.mobifin_busticket.AppData;
import com.example.mobifin_busticket.UserInfo;
import com.example.mobifin_busticket.models.Boarding_DropingPointModel;
import com.example.mobifin_busticket.models.BusModel;
import com.example.mobifin_busticket.models.FareDetailsModel;
import com.example.mobifin_busticket.models.FareModel;
import com.example.mobifin_busticket.models.SourceModel;

public class DatabaseManager {

	private static final String DATABASE_NAME = "MobifinDB.db";
	private static final int DATABASE_VERSION = 5;
	private static final String DROPIFEXISTS = "DROP TABLE IF EXISTS ";
	private static final String REGISTRATIONTABLE = "UserInfo";
	private static final String INSERTINTO = "insert into ";
	private static final String CREATETABLE = "CREATE TABLE ";
	private static final String TABLE_ALL_SOURCES = "tbl_all_sources";
	private static final String TABLE_RECENT_SOURCES = "tbl_recent_sources";
	private static final String TABLE_BUS_LIST = "tbl_bus_list";
	private static final String TABLE_BORADING_LIST = "tbl_boarding_list";
	private static final String TABLE_DROPING_LIST = "tbl_droping_list";
	private static final String TABLE_FARE_DETAILS = "tbl_faredetails";
	private static final String TABLE_FARE = "tbl_fare";

	private static final String INSERT_USERINFO = INSERTINTO
			+ REGISTRATIONTABLE
			+ "(Id,Phone_No,Email,Terminal_No,Password,Transaction_pin,terminalnumber,encryptionkey) values (?,?,?,?,?,?,?,?)";

	private static final String INSERT_Sources = INSERTINTO + TABLE_ALL_SOURCES
			+ "(id,sourcename) values (?,?)";
	private static final String INSERT_Recent_Sources = INSERTINTO
			+ TABLE_RECENT_SOURCES + "(id,sourcename) values (?,?)";
	private static final String INSERT_TripDetails = INSERTINTO
			+ TABLE_BUS_LIST

			+ "(Bus_id,AC,arrivalTime,availableSeats,busType,busTypeId,cancellationPolicy,departureTime,doj,idProofRequired,liveTrackingAvailable,nonAC,operator,partialCancellationAllowed,routeId,seater,sleeper,travels,mTicketEnabled) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	private static final String INSERT_BoardingDetails = INSERTINTO
			+ TABLE_BORADING_LIST
			+ "(busid,bpId,bpName,location,prime,time) values (?,?,?,?,?,?)";

	private static final String INSERT_DropingDetails = INSERTINTO
			+ TABLE_DROPING_LIST
			+ "(busid,bpId,bpName,location,prime,time) values (?,?,?,?,?,?)";

	private static final String INSERT_FareDetails = INSERTINTO
			+ TABLE_FARE_DETAILS
			+ "(busid,baseFare,serviceTaxAbsolute,serviceTaxPercentage,totalFare) values (?,?,?,?,?)";

	private static final String INSERT_Fare = INSERTINTO + TABLE_FARE
			+ "(busid,fares) values (?,?)";
	private Context context;
	private SQLiteDatabase db;
	private SQLiteStatement insertStmtseatinfo;

	OpenHelper openHelper;

	public SQLiteDatabase getDB() {
		if (db != null) {
			return db;
		} else {
			return null;
		}
	}

	public DatabaseManager(Context context) {
		this.context = context;
		openHelper = new OpenHelper(this.context);
		this.db = openHelper.getWritableDatabase();

	}

	private class OpenHelper extends SQLiteOpenHelper {
		OpenHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL("CREATE TABLE IF NOT EXISTS "
					+ REGISTRATIONTABLE
					+ " (Id varchar,Phone_No number,Email varchar,Terminal_No number,Password number,Transaction_pin number,terminalnumber number,encryptionkey number);");
			db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_ALL_SOURCES
					+ " (id varchar,sourcename varchar);");
			db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_RECENT_SOURCES
					+ " (id varchar,sourcename varchar);");

			db.execSQL("CREATE TABLE IF NOT EXISTS "
					+ TABLE_BUS_LIST
					+ " (_id INTEGER PRIMARY KEY,Bus_id TEXT,AC TEXT,arrivalTime TEXT,availableSeats TEXT,busType TEXT,busTypeId TEXT,cancellationPolicy TEXT,departureTime TEXT,doj TEXT,idProofRequired TEXT,liveTrackingAvailable TEXT,nonAC TEXT,operator TEXT,partialCancellationAllowed TEXT,routeId TEXT,seater TEXT,sleeper TEXT,travels TEXT,mTicketEnabled TEXT);");

			db.execSQL("CREATE TABLE IF NOT EXISTS "
					+ TABLE_BORADING_LIST
					+ " (_id INTEGER PRIMARY KEY,busid TEXT,bpId TEXT,bpName TEXT,location TEXT,prime TEXT,time TEXT);");

			db.execSQL("CREATE TABLE IF NOT EXISTS "
					+ TABLE_DROPING_LIST
					+ " (_id INTEGER PRIMARY KEY,busid TEXT,bpId TEXT,bpName TEXT,location TEXT,prime TEXT,time TEXT);");

			db.execSQL("CREATE TABLE IF NOT EXISTS "
					+ TABLE_FARE_DETAILS
					+ " (_id INTEGER PRIMARY KEY,busid TEXT,baseFare TEXT,serviceTaxAbsolute TEXT,serviceTaxPercentage TEXT,totalFare TEXT);");

			db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_FARE
					+ " (_id INTEGER PRIMARY KEY,busid TEXT,fares REAL);");
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w("Example",
					"Upgrading database, this will drop tables and recreate.");
			db.execSQL(DROPIFEXISTS + REGISTRATIONTABLE);
			db.execSQL(DROPIFEXISTS + TABLE_ALL_SOURCES);
			db.execSQL(DROPIFEXISTS + TABLE_BUS_LIST);

			onCreate(db);

		}
	}

	public void close() {
		if (openHelper != null) {
			openHelper.close();
		}
	}

	public void insertUserInfo(String kycinfo, String no, String mail,
			String tno, String pass, String pin, String terminalreqId,
			String encryptionKey) {
		SQLiteStatement statementbd = this.db.compileStatement(INSERT_USERINFO);
		db.beginTransaction();
		statementbd.bindString(1, "" + kycinfo);
		statementbd.bindString(2, "" + no);
		statementbd.bindString(3, "" + mail);
		statementbd.bindString(4, "" + tno);
		statementbd.bindString(5, "" + pass);
		statementbd.bindString(6, "" + pin);
		statementbd.bindString(7, "" + terminalreqId);
		statementbd.bindString(8, "" + encryptionKey);
		statementbd.execute();
		statementbd.clearBindings();

		db.setTransactionSuccessful();
		db.endTransaction();
	}

	public ArrayList<SourceModel> getSourceFromDB() {
		// TODO Auto-generated method stub
		System.out.println("get source from db");
		Cursor cursor = db.rawQuery("select * from " + TABLE_ALL_SOURCES
				+ " ORDER BY sourcename ASC ", null);
		ArrayList<SourceModel> sourceList = new ArrayList<SourceModel>();

		if (cursor.getCount() > 0) {
			if (cursor.moveToFirst()) {
				do {
					SourceModel source = new SourceModel();
					source.setId(cursor.getString(0));
					source.setName(cursor.getString(1));
					sourceList.add(source);
				} while (cursor.moveToNext());
			}
			if (cursor != null && !cursor.isClosed()) {
				cursor.close();
			}
			return sourceList;
		} else {
			return sourceList;
		}
	}

	public UserInfo getUserInfo() {

		Cursor cursor = db.rawQuery("select * from " + REGISTRATIONTABLE, null);

		UserInfo userInfo = new UserInfo();
		if (cursor.getCount() > 0) {
			if (cursor.moveToFirst()) {
				do {

					userInfo.seteMail(cursor.getString(2));
					userInfo.setTerminalNumber(cursor.getString(3));
					userInfo.setLoginPassword((cursor.getString(4)));
					userInfo.setTransactionPassword(cursor.getString(5));
					userInfo.setEncryptionKey((cursor.getString(7)));

				} while (cursor.moveToNext());
			}
			if (cursor != null && !cursor.isClosed()) {
				cursor.close();
			}
			return userInfo;
		} else {
			return null;
		}
	}

	public void insertSources(String id, String sourcename) {
		SQLiteStatement statementbd = this.db.compileStatement(INSERT_Sources);
		db.beginTransaction();
		statementbd.bindString(1, "" + id);
		statementbd.bindString(2, "" + sourcename);

		statementbd.execute();
		statementbd.clearBindings();

		db.setTransactionSuccessful();
		db.endTransaction();

	}

	public void flushTable() {
		// TODO Auto-generated method stub
		this.db.delete(REGISTRATIONTABLE, null, null);
		this.db.delete(TABLE_ALL_SOURCES, null, null);
		this.db.delete(TABLE_RECENT_SOURCES, null, null);

	}

	public void insertAllTrip(ArrayList<BusModel> busList) {
		// TODO Auto-generated method stub
		SQLiteStatement statementbd = this.db
				.compileStatement(INSERT_TripDetails);
		db.beginTransaction();
		for (int i = 0; i < busList.size(); i++) {

			if (i == 9) {
				System.out.println();
			}
			System.out.println("Inserting " + i + 1 + "record");
			statementbd.bindString(1, "" + busList.get(i).getBus_id());
			statementbd.bindString(2, "" + busList.get(i).getACValue());
			statementbd.bindString(3, "" + busList.get(i).getarrivalTime());
			statementbd.bindString(4, "" + busList.get(i).getavailableSeats());
			statementbd.bindString(5, "" + busList.get(i).getbusType());
			statementbd.bindString(6, "" + busList.get(i).getbusTypeId());
			statementbd.bindString(7, ""
					+ busList.get(i).getcancellationPolicy());
			statementbd.bindString(8, "" + busList.get(i).getdepartureTime());
			statementbd.bindString(9, "" + busList.get(i).getdoj());
			statementbd
					.bindString(10, "" + busList.get(i).getidProofRequired());
			statementbd.bindString(11, ""
					+ busList.get(i).getliveTrackingAvailable());
			statementbd.bindString(12, "" + busList.get(i).getnonAC());
			statementbd.bindString(13, "" + busList.get(i).getoperator());
			statementbd.bindString(14, ""
					+ busList.get(i).getpartialCancellationAllowed());
			statementbd.bindString(15, "" + busList.get(i).getrouteId());
			statementbd.bindString(16, "" + busList.get(i).getseater());
			statementbd.bindString(17, "" + busList.get(i).getsleeper());
			statementbd.bindString(18, "" + busList.get(i).gettravels());
			statementbd.bindString(19, "" + busList.get(i).getmTicketEnabled());

			statementbd.execute();
			statementbd.clearBindings();

			ArrayList<Boarding_DropingPointModel> boardingPoint = busList
					.get(i).getBoardingPointList();
			insertBoardingTimeInfo(boardingPoint);

			ArrayList<Boarding_DropingPointModel> dropingPoint = busList.get(i)
					.getDropingPointList();
			insertDropingTimeInfo(dropingPoint);

			ArrayList<FareDetailsModel> fareDetailsList = busList.get(i)
					.getFaredetailsList();
			insertfareDetailsInfo(fareDetailsList);

			ArrayList<FareModel> fareList = busList.get(i).getFareList();
			insertfareInfo(fareList);

		}
		db.setTransactionSuccessful();
		db.endTransaction();
	}

	public void insertBoardingTimeInfo(
			ArrayList<Boarding_DropingPointModel> boardingPointList) {
		// TODO Auto-generated method stub

		SQLiteStatement statementbd = this.db
				.compileStatement(INSERT_BoardingDetails);
		db.beginTransaction();
		for (int i = 0; i < boardingPointList.size(); i++) {
			statementbd.bindString(1, "" + boardingPointList.get(i).getBusId());
			statementbd.bindString(2, ""
					+ boardingPointList.get(i).getBoardingPointId());
			statementbd.bindString(3, ""
					+ boardingPointList.get(i).getBordingPointName());
			statementbd.bindString(4, ""
					+ boardingPointList.get(i).getlocation());
			statementbd.bindString(5, "" + boardingPointList.get(i).getprime());
			statementbd.bindString(6, "" + boardingPointList.get(i).gettime());

			statementbd.execute();
			statementbd.clearBindings();
		}
		db.setTransactionSuccessful();
		db.endTransaction();
	}

	public void insertDropingTimeInfo(
			ArrayList<Boarding_DropingPointModel> dropingPointList) {
		// TODO Auto-generated method stub

		SQLiteStatement statementbd = this.db
				.compileStatement(INSERT_DropingDetails);
		db.beginTransaction();
		for (int i = 0; i < dropingPointList.size(); i++) {
			statementbd.bindString(1, "" + dropingPointList.get(i).getBusId());
			statementbd.bindString(2, ""
					+ dropingPointList.get(i).getBoardingPointId());
			statementbd.bindString(3, ""
					+ dropingPointList.get(i).getBordingPointName());
			statementbd.bindString(4, ""
					+ dropingPointList.get(i).getlocation());
			statementbd.bindString(5, "" + dropingPointList.get(i).getprime());
			statementbd.bindString(6, "" + dropingPointList.get(i).gettime());

			statementbd.execute();
			statementbd.clearBindings();
		}
		db.setTransactionSuccessful();
		db.endTransaction();
	}

	public void insertfareDetailsInfo(
			ArrayList<FareDetailsModel> faredetailsList) {
		// TODO Auto-generated method stub
		SQLiteStatement statementbd = this.db
				.compileStatement(INSERT_FareDetails);
		db.beginTransaction();
		for (int i = 0; i < faredetailsList.size(); i++) {
			statementbd.bindString(1, "" + faredetailsList.get(i).getBusId());
			statementbd
					.bindString(2, "" + faredetailsList.get(i).getbaseFare());
			statementbd.bindString(3, ""
					+ faredetailsList.get(i).getserviceTaxAbsolute());
			statementbd.bindString(4, ""
					+ faredetailsList.get(i).getserviceTaxPercentage());
			statementbd.bindString(5, ""
					+ faredetailsList.get(i).gettotalFare());

			statementbd.execute();
			statementbd.clearBindings();
		}
		db.setTransactionSuccessful();
		db.endTransaction();
	}

	public void insertfareInfo(ArrayList<FareModel> fareList) {
		// TODO Auto-generated method stub
		SQLiteStatement statementbd = this.db.compileStatement(INSERT_Fare);
		db.beginTransaction();
		for (int i = 0; i < fareList.size(); i++) {
			statementbd.bindString(1, "" + fareList.get(i).getBusId());
			statementbd.bindString(2, "" + fareList.get(i).getfare());

			statementbd.execute();
			statementbd.clearBindings();
		}
		db.setTransactionSuccessful();
		db.endTransaction();
	}

	public void deleteAllavailableTrips() {
		db.beginTransaction();

		this.db.delete(TABLE_BUS_LIST, null, null);

		this.db.delete(TABLE_BORADING_LIST, null, null);
		this.db.delete(TABLE_FARE_DETAILS, null, null);
		this.db.delete(TABLE_FARE, null, null);
		this.db.delete(TABLE_DROPING_LIST, null, null);

		db.setTransactionSuccessful();
		db.endTransaction();
	}

	public ArrayList<BusModel> getBusListFromDB() {
		// TODO Auto-generated method stub
		System.out.println("get bus details from db");
		Cursor cursor = db.rawQuery("select * from " + TABLE_BUS_LIST, null);
		ArrayList<BusModel> BusList = new ArrayList<BusModel>();

		if (cursor.getCount() > 0) {
			if (cursor.moveToFirst()) {
				do {
					BusModel bus = new BusModel();

					bus.setBus_id(cursor.getString(1));
					bus.setACValue(cursor.getString(2));
					bus.setarrivalTime(cursor.getString(3));
					bus.setavailableSeats(cursor.getInt(4));
					bus.setbusType(cursor.getString(5));
					bus.setbusTypeId(cursor.getString(6));
					bus.setcancellationPolicy(cursor.getString(7));
					bus.setdepartureTime(cursor.getString(8));
					bus.setdoj(cursor.getString(9));
					bus.setidProofRequired(cursor.getString(10));
					bus.setliveTrackingAvailable(cursor.getString(11));
					bus.setnonAC(cursor.getString(12));
					bus.setoperator(cursor.getString(13));
					bus.setpartialCancellationAllowed(cursor.getString(14));
					bus.setrouteId(cursor.getString(15));
					bus.setseater(cursor.getString(16));
					bus.setsleeper(cursor.getString(17));
					bus.settravels(cursor.getString(18));
					bus.setmTicketEnabled(cursor.getString(19));

					ArrayList<FareDetailsModel> fareDetailsList = getFareDetails(bus
							.getBus_id());
					bus.setFaredetailsList(fareDetailsList);

					ArrayList<FareModel> farelist = getFare(bus.getBus_id());
					bus.setFareList(farelist);

					ArrayList<Boarding_DropingPointModel> BoardingList = getBoardingDetails(bus
							.getBus_id());
					bus.setBoardingPointList(BoardingList);

					ArrayList<Boarding_DropingPointModel> DropingList = getDropingDetails(bus
							.getBus_id());
					bus.setBoardingPointList(DropingList);

					BusList.add(bus);
				} while (cursor.moveToNext());
			}
			if (cursor != null && !cursor.isClosed()) {
				cursor.close();
			}
			return BusList;
		} else {
			return BusList;
		}
	}

	private ArrayList<Boarding_DropingPointModel> getDropingDetails(
			String bus_id) {
		// TODO Auto-generated method stub
		System.out.println("get Boarding point from db for bus ID" + bus_id);
		Cursor cursor = db.rawQuery("select * from " + TABLE_DROPING_LIST
				+ " where busid=" + bus_id, null);
		ArrayList<Boarding_DropingPointModel> DropingList = new ArrayList<Boarding_DropingPointModel>();

		if (cursor.getCount() > 0) {
			if (cursor.moveToFirst()) {
				do {

					Boarding_DropingPointModel DropingModel = new Boarding_DropingPointModel();

					DropingModel.setBusId(cursor.getString(1));
					DropingModel.setBoardingPointId(cursor.getString(2));
					DropingModel.setBordingPointName(cursor.getString(3));
					DropingModel.setlocation(cursor.getString(4));
					DropingModel.setprime(cursor.getString(5));
					DropingModel.settime(cursor.getString(6));

					DropingList.add(DropingModel);
				} while (cursor.moveToNext());
			}
			if (cursor != null && !cursor.isClosed()) {
				cursor.close();
			}
			return DropingList;
		} else {
			return DropingList;
		}
	}

	private ArrayList<Boarding_DropingPointModel> getBoardingDetails(
			String bus_id) {
		// TODO Auto-generated method stub
		System.out.println("get Boarding point from db for bus ID" + bus_id);
		Cursor cursor = db.rawQuery("select * from " + TABLE_BORADING_LIST
				+ " where busid=" + bus_id, null);
		ArrayList<Boarding_DropingPointModel> BoardingList = new ArrayList<Boarding_DropingPointModel>();

		if (cursor.getCount() > 0) {
			if (cursor.moveToFirst()) {
				do {

					Boarding_DropingPointModel BordingModel = new Boarding_DropingPointModel();

					BordingModel.setBusId(cursor.getString(1));
					BordingModel.setBoardingPointId(cursor.getString(2));
					BordingModel.setBordingPointName(cursor.getString(3));
					BordingModel.setlocation(cursor.getString(4));
					BordingModel.setprime(cursor.getString(5));
					BordingModel.settime(cursor.getString(6));

					BoardingList.add(BordingModel);
				} while (cursor.moveToNext());
			}
			if (cursor != null && !cursor.isClosed()) {
				cursor.close();
			}
			return BoardingList;
		} else {
			return BoardingList;
		}
	}

	private ArrayList<FareModel> getFare(String bus_id) {
		// TODO Auto-generated method stub
		System.out.println("get Fare from db for bus ID" + bus_id);
		Cursor cursor = db.rawQuery("select * from " + TABLE_FARE
				+ " where busid=" + bus_id + " GROUP BY busid ", null);
		ArrayList<FareModel> fareList = new ArrayList<FareModel>();

		if (cursor.getCount() > 0) {
			if (cursor.moveToFirst()) {
				do {

					FareModel fareModel = new FareModel();

					fareModel.setBusId(cursor.getString(1));
					fareModel.setfare(cursor.getString(2));

					fareList.add(fareModel);
				} while (cursor.moveToNext());
			}
			if (cursor != null && !cursor.isClosed()) {
				cursor.close();
			}
			return fareList;
		} else {
			return fareList;
		}
	}

	private ArrayList<FareDetailsModel> getFareDetails(String bus_id) {

		System.out.println("get FareDetails from db for bus ID" + bus_id);
		Cursor cursor = db.rawQuery("select * from " + TABLE_FARE_DETAILS
				+ " where busid=" + bus_id, null);
		ArrayList<FareDetailsModel> faredetailsList = new ArrayList<FareDetailsModel>();

		if (cursor.getCount() > 0) {
			if (cursor.moveToFirst()) {
				do {

					FareDetailsModel fareDetildModel = new FareDetailsModel();

					fareDetildModel.setBusId(cursor.getString(1));
					fareDetildModel.setbaseFare(cursor.getString(2));
					fareDetildModel.setserviceTaxAbsolute(cursor.getString(3));
					fareDetildModel
							.setserviceTaxPercentage(cursor.getString(4));
					fareDetildModel.settotalFare(cursor.getString(5));
					faredetailsList.add(fareDetildModel);
				} while (cursor.moveToNext());
			}
			if (cursor != null && !cursor.isClosed()) {
				cursor.close();
			}
			return faredetailsList;
		} else {
			return faredetailsList;
		}

	}

	public List<String> getTravelsName() {
		List<String> strTravelName = null;

		Cursor cursor = db.rawQuery("Select travels from " + TABLE_BUS_LIST
				+ " GROUP BY travels", null);

		strTravelName = new ArrayList<String>();

		if (cursor.getCount() > 0) {

			if (cursor.moveToFirst() && !cursor.isAfterLast()) {
				do {
					strTravelName.add(cursor.getString(0));
				} while (cursor.moveToNext());
			}
			if (cursor != null && !cursor.isClosed()) {
				cursor.close();
			}
			return strTravelName;
		} else {

			return strTravelName;
		}
	}

	public List<String> getBoardingName() {
		// TODO Auto-generated method stub
		List<String> strBoardingPoints = null;

		Cursor cursor = db.rawQuery("Select bpName from " + TABLE_BORADING_LIST
				+ " GROUP BY bpName", null);

		strBoardingPoints = new ArrayList<String>();

		if (cursor.getCount() > 0) {

			if (cursor.moveToFirst() && !cursor.isAfterLast()) {
				do {
					strBoardingPoints.add(cursor.getString(0));
				} while (cursor.moveToNext());
			}
			if (cursor != null && !cursor.isClosed()) {
				cursor.close();
			}
			return strBoardingPoints;
		} else {

			return strBoardingPoints;
		}

	}

	public List<String> getDropingName() {
		// TODO Auto-generated method stub
		List<String> strdropingPoints = null;

		Cursor cursor = db.rawQuery("Select bpName from " + TABLE_DROPING_LIST
				+ " GROUP BY bpName", null);

		strdropingPoints = new ArrayList<String>();

		if (cursor.getCount() > 0) {

			if (cursor.moveToFirst() && !cursor.isAfterLast()) {
				do {
					strdropingPoints.add(cursor.getString(0));
				} while (cursor.moveToNext());
			}
			if (cursor != null && !cursor.isClosed()) {
				cursor.close();
			}
			return strdropingPoints;
		} else {

			return strdropingPoints;
		}
	}

	public ArrayList<BusModel> getFilterSearch(String ac, String NonAc,
			String sleeper, String nonSleeper, ArrayList<String> travel,
			ArrayList<String> boarding, ArrayList<String> droping) {
		// TODO Auto-generated method stub

		ArrayList<BusModel> busList = new ArrayList<BusModel>();
		String strList, strButton;
		String rawQuery = "SELECT t1.*,GROUP_CONCAT(DISTINCT t1.fares),Min(t1.fares),t1.Bus_id "
				+ " FROM (SELECT tbl_bus_list.*, tbl_fare.fares ,tbl_fare.busid "
				+ " FROM  tbl_bus_list, tbl_fare WHERE (tbl_bus_list.Bus_id= tbl_fare.busid) "
				+ " ORDER BY tbl_bus_list.Bus_id ASC, tbl_fare.fares asc) t1 "
				+ "LEFT JOIN tbl_droping_list as d on t1.Bus_id=d.busid LEFT JOIN tbl_boarding_list as b on t1.Bus_id=b.busid "
				+ "LEFT JOIN tbl_bus_list as t on t.Bus_id=t1.Bus_id";

		String strand = " and ";
		String wheremain = " where ";
		strButton = getAvaliableTripsFilterForButton(ac, NonAc, sleeper,
				nonSleeper, strand);
		strList = getAvaliableTripsFilterForList(travel, boarding, droping,
				strand);
		String grpID = " GROUP BY t1.busid order by Min(t1.fares) ";
		System.out.println("get Busdatails from db for Ac:" + ac);
		System.out.println("get Busdatails from db for nonAc:" + NonAc);
		System.out.println("get Busdatails from db for sleeper:" + sleeper);
		System.out.println("get Busdatails from db for Seater:" + nonSleeper);
		System.out.println("get Travels from db: " + travel);
		System.out.println("get boarding from db:" + boarding);
		System.out.println("get droping from db:" + droping);


		if (!strButton.isEmpty() && !strList.isEmpty()) {
			rawQuery = rawQuery + wheremain + strButton + strand + strList
					+ grpID;
		} else if (!strButton.isEmpty()) {
			rawQuery = rawQuery + wheremain + strButton + grpID;
		} else if (!strList.isEmpty()) {
			rawQuery = rawQuery + wheremain + strList + grpID;
		}

		Log.d("finale query", rawQuery);
		Cursor cursor = db.rawQuery(rawQuery, null);
		if (cursor.getCount() > 0) {
			if (cursor.moveToFirst()) {
				do {
					BusModel bus = new BusModel();

					bus.setBus_id(cursor.getString(1));
					bus.setACValue(cursor.getString(2));
					bus.setarrivalTime(cursor.getString(3));
					bus.setavailableSeats(cursor.getInt(4));
					bus.setbusType(cursor.getString(5));
					bus.setbusTypeId(cursor.getString(6));
					bus.setcancellationPolicy(cursor.getString(7));
					bus.setdepartureTime(cursor.getString(8));
					bus.setdoj(cursor.getString(9));
					bus.setidProofRequired(cursor.getString(10));
					bus.setliveTrackingAvailable(cursor.getString(11));
					bus.setnonAC(cursor.getString(12));
					bus.setoperator(cursor.getString(13));
					bus.setpartialCancellationAllowed(cursor.getString(14));
					bus.setrouteId(cursor.getString(15));
					bus.setseater(cursor.getString(16));
					bus.setsleeper(cursor.getString(17));
					bus.settravels(cursor.getString(18));
					bus.setmTicketEnabled(cursor.getString(19));

					ArrayList<FareDetailsModel> fareDetailsList = getFareDetails(bus
							.getBus_id());
					bus.setFaredetailsList(fareDetailsList);

					ArrayList<FareModel> farelist = getFare(bus.getBus_id());
					bus.setFareList(farelist);

					ArrayList<Boarding_DropingPointModel> BoardingList = getBoardingDetails(bus
							.getBus_id());
					bus.setBoardingPointList(BoardingList);

					ArrayList<Boarding_DropingPointModel> DropingList = getDropingDetails(bus
							.getBus_id());
					bus.setBoardingPointList(DropingList);
					busList.add(bus);
				} while (cursor.moveToNext());
			}
			if (cursor != null && !cursor.isClosed()) {
				cursor.close();
			}
			return busList;
		} else {
			return busList;
		}

	}

	private String getAvaliableTripsFilterForList(ArrayList<String> travel,
			ArrayList<String> boarding, ArrayList<String> droping, String strand) {
		// TODO Auto-generated method stub
		String travelsin = "t.travels in ";
		String bpnamein = "b.bpName in ";
		String dpnamein = "d.bpName in ";
		String rawQuery = "";
		String strbp = "";
		String strdp = "";
		String strtravels = "";
		if (travel != null && boarding != null && droping != null) {
			strtravels = mgetTravelsString(travel);
			strbp = mgetBoardingString(boarding);
			strdp = mgetDropingString(droping);
			rawQuery = travelsin + "(" + strtravels + ")" + strand + bpnamein
					+ "(" + strbp + ")" + strand + dpnamein + "(" + strdp + ")";
		}
		else if (travel != null && boarding != null) {
			strtravels = mgetTravelsString(travel);
			strbp = mgetBoardingString(boarding);
			rawQuery = travelsin + "(" + strtravels + ")" + strand + bpnamein
					+ "(" + strbp + ")";
		}
		else if (travel != null && droping != null) {
			strtravels = mgetTravelsString(travel);
			strdp = mgetDropingString(droping);
			rawQuery = travelsin + "(" + strtravels + ")" + strand + dpnamein
					+ "(" + strdp + ")";
		}
		else if (boarding != null && droping != null) {
			strbp = mgetBoardingString(boarding);
			strdp = mgetDropingString(droping);
			rawQuery = bpnamein + "(" + strbp + ")" + strand + dpnamein + "("
					+ strdp + ")";
		} else {
			rawQuery = getAvaliableTripsFilterForListSub(travel, strtravels,
					boarding, strbp, droping, strdp);
		}
		return rawQuery;
	}

	private String getAvaliableTripsFilterForListSub(ArrayList<String> travel,
			String strtravels1, ArrayList<String> boarding, String strbp1,
			ArrayList<String> droping, String strdp1) {
		// TODO Auto-generated method stub
		String travelsin = "t.travels in ";
		String bpnamein = "b.bpName in ";
		String dpnamein = "d.bpName in ";
		String rawQuery = "";
		String strbp = strbp1;
		String strdp = strdp1;
		String strtravels = strtravels1;
		if (travel != null) {
			strtravels = mgetTravelsString(travel);
			rawQuery = travelsin + "(" + strtravels + ")";
		}
		if (boarding != null) {
			strbp = mgetBoardingString(boarding);
			rawQuery = bpnamein + "(" + strbp + ")";
		}
		if (droping != null) {
			strdp = mgetDropingString(droping);
			rawQuery = dpnamein + "(" + strdp + ")";
		}
		return rawQuery;
	}

	private String getAvaliableTripsFilterForButton(String ac, String nonAc,
			String sleeper, String nonSleeper, String strand) {
		// TODO Auto-generated method stub
		String strac = "t.AC ";
		String strnonac = "t.nonAC  ";
		String strseater = "t.seater  ";
		String strsleeper = "t.sleeper ";
		String rawQuery = "";
		if (ac.equals("true") && sleeper.equals("true")) {

			rawQuery = strac + "='" + ac + "'" + strand + strsleeper + "='"
					+ sleeper + "'";
		} else if (nonAc.equals("true") && nonSleeper.equals("true")) {
			rawQuery = strnonac + "='" + nonAc + "'" + strand + strseater
					+ "='" + nonSleeper + "'";
		} else if (sleeper.equals("true") && nonAc.equals("true")) {
			rawQuery = strsleeper + "='" + sleeper + "'" + strand + strnonac
					+ "='" + nonAc + "'";
		} else if (nonSleeper.equals("true") && ac.equals("true")) {
			rawQuery = strseater + "='" + nonSleeper + "'" + strand + strac
					+ "='" + ac + "'";
		} else {
			rawQuery = getAvaliableTripsFilterForButtonSub(ac, nonAc, sleeper,
					nonSleeper, strand);
		}

		return rawQuery;
	}

	private String getAvaliableTripsFilterForButtonSub(String ac, String nonAc,
			String sleeper, String nonSleeper, String strand) {
		// TODO Auto-generated method stub
		String strac = "t.AC ";
		String strnonac = "t.nonAC  ";
		String strseater = "t.seater  ";
		String strsleeper = "t.sleeper ";
		String rawQuery = "";
		if (ac.equals("true")) {

			rawQuery = strac + "='" + ac + "'";
		} else if (nonAc.equals("true")) {
			rawQuery = strnonac + "='" + nonAc + "'";
		} else if (sleeper.equals("true")) {
			rawQuery = strsleeper + "='" + sleeper + "'";
		} else if (nonSleeper.equals("true")) {
			rawQuery = strseater + "='" + nonSleeper + "'";
		}
		return rawQuery;
	}

	private String mgetTravelsString(ArrayList<String> travel) {
		String travelslist = null;
		StringBuilder sbtravels = new StringBuilder();
		if (!travel.isEmpty()) {
			int i = 0;
			do {
				sbtravels.append("\'" + travel.get(i).toString() + "\'");
				if (i != travel.size() - 1) {
					sbtravels.append(",");
				}
				i++;
			} while (i < travel.size());
			travelslist = sbtravels.toString();
			travelslist.split(" ");
			System.out.println("travel list:" + travelslist);
		}
		return travelslist;
	}

	private String mgetBoardingString(ArrayList<String> boarding) {
		String Boardinglist = null;
		StringBuilder sbboarding = new StringBuilder();
		if (!boarding.isEmpty()) {
			int i = 0;
			do {
				sbboarding.append("\'" + boarding.get(i).toString() + "\'");
				if (i != boarding.size() - 1) {
					sbboarding.append(",");
				}
				i++;
			} while (i < boarding.size());
			Boardinglist = sbboarding.toString();

		}
		return Boardinglist;
	}

	private String mgetDropingString(ArrayList<String> droping) {
		String Dropinglist = null;
		StringBuilder sbdroping = new StringBuilder();
		if (!droping.isEmpty()) {
			int i = 0;
			do {
				sbdroping.append("\'" + droping.get(i).toString() + "\'");
				if (i != droping.size() - 1) {
					sbdroping.append(",");
				}
				i++;
			} while (i < droping.size());
			Dropinglist = sbdroping.toString();

		}
		return Dropinglist;
	}

	public ArrayList<Boarding_DropingPointModel> getBoardingPoint(String busId) {
		// TODO Auto-generated method stub
		System.out.println("get boarding from db");
		Cursor cursor = db.rawQuery(
				"select bpId,bpName,time from tbl_boarding_list where busid='"
						+ busId + "' ORDER BY bpName ASC ", null);
		ArrayList<Boarding_DropingPointModel> boardingPoint = new ArrayList<Boarding_DropingPointModel>();

		if (cursor.getCount() > 0) {
			if (cursor.moveToFirst()) {
				do {
					Boarding_DropingPointModel boardingModel = new Boarding_DropingPointModel();
					AppData.BoardingPointId = cursor.getString(0);
					System.out.println("b id from datebase:"
							+ AppData.BoardingPointId);
					boardingModel.setBordingPointName(cursor.getString(1));
					boardingModel.settime(cursor.getString(2));
					boardingPoint.add(boardingModel);
				} while (cursor.moveToNext());
			}
			if (cursor != null && !cursor.isClosed()) {
				cursor.close();
			}
			return boardingPoint;
		} else {
			return boardingPoint;

		}
	}

	public BusModel getCancelltionPolicy(String busId) {
		// TODO Auto-generated method stub
		System.out.println("get cancelltionPOlicy from db");

		Cursor cursor = db
				.rawQuery(
						"select cancellationPolicy,departureTime,partialCancellationAllowed,doj from tbl_bus_list where Bus_id='"
								+ busId + "'", null);
		BusModel busModel = new BusModel();

		if (cursor.getCount() > 0) {
			if (cursor.moveToFirst()) {
				do {

					busModel.setcancellationPolicy(cursor.getString(0));
					busModel.setdepartureTime(cursor.getString(1));
					busModel.setpartialCancellationAllowed(cursor.getString(2));
					busModel.setdoj(cursor.getString(3));
					System.out.println("CancellationPolisy list:" + busModel);
				} while (cursor.moveToNext());
			}
			if (cursor != null && !cursor.isClosed()) {
				cursor.close();
			}

		}
		return busModel;

	}

	public void insertRecentCiry(String recentCityId, String recentCityName) {
		// TODO Auto-generated method stub
		SQLiteStatement statementbd = this.db
				.compileStatement(INSERT_Recent_Sources);
		db.beginTransaction();
		statementbd.bindString(1, "" + recentCityId);
		statementbd.bindString(2, "" + recentCityName);

		statementbd.execute();
		statementbd.clearBindings();

		db.setTransactionSuccessful();
		db.endTransaction();
	}

	public ArrayList<SourceModel> getRecentSourceFromDB() {
		// TODO Auto-generated method stub
		System.out.println("get recent source from db");
		Cursor cursor = db.rawQuery("select DISTINCT sourcename,id from "
				+ TABLE_RECENT_SOURCES + " ORDER BY sourcename ASC ", null);
		ArrayList<SourceModel> RecentsourceList = new ArrayList<SourceModel>();

		if (cursor.getCount() > 0) {
			if (cursor.moveToFirst()) {
				do {
					SourceModel source = new SourceModel();
					source.setRecentCityId(cursor.getString(1));
					source.setRecentCityName(cursor.getString(0));
					RecentsourceList.add(source);
				} while (cursor.moveToNext());
			}
			if (cursor != null && !cursor.isClosed()) {
				cursor.close();
			}
			return RecentsourceList;
		} else {
			return RecentsourceList;
		}

	}

	public boolean checkTableValues(String tabelName, String coloumnName) {
		Cursor cursor = this.db.query(tabelName, new String[] { coloumnName },
				null, null, null, null, null);
		if (cursor.getCount() > 0) {
			if (cursor.moveToFirst()) {
				Log.d(AppData.DEBUG, cursor.getString(0) + " is in "
						+ tabelName + " Table..");
			}
			if (cursor != null && !cursor.isClosed()) {
				cursor.close();
			}
			Log.d(AppData.DEBUG, "Returning True in " + tabelName + " table..");
			return true;
		} else {
			Log.d(AppData.DEBUG, tabelName
					+ " Table is empty..,Returning false..");
			return false;
		}
	}

	public boolean checkRecentSources() {
		return checkTableValues(TABLE_RECENT_SOURCES, "sourcename");
	}

	public void deleteFromRecentSearch(String recentCityName) {
		// TODO Auto-generated method stub
		SQLiteStatement statementbd = this.db
				.compileStatement("DELETE FROM tbl_recent_sources where sourcename='"
						+ recentCityName + "'");
		db.beginTransaction();

		statementbd.execute();
		statementbd.clearBindings();

		db.setTransactionSuccessful();
		db.endTransaction();

	}
}
